<?php

class Renew_Model_Shinchaku
{

    public function getShinchakuSelect()
    {
        $sql = " SELECT";
        $sql .= "     info1.T03_JYUUSHO_TEXT";
        $sql .= "    ,info1.T03_HEYA_CD";
        $sql .= "    ,info1.T03_GAIKAN_GAZOU01";
        $sql .= "    ,'5' || SUBSTR(info1.T03_TENPO_CD,1,2) || SUBSTR(info1.T03_TENPO_CD,4,5) || '00' AS T03_TENPO_CD";
        $sql .= "    ,info1.T03_TENPO_CD AS M05_TENPO_CD";
        $sql .= "    ,info1.T03_TENPO_NAME";
        $sql .= "    ,CASE ";
        $sql .= "        WHEN info1.T03_YACHIN >=10000 THEN TO_CHAR(info1.T03_YACHIN / 10000) ";
        $sql .= "        ELSE  '0' || TO_CHAR(info1.T03_YACHIN / 10000) ";
        $sql .= "        END T03_YACHIN";
        $sql .= "    ,CASE";
        $sql .= "        WHEN (";
        $sql .= "            info1.T03_KANRIHI IS null";
        $sql .= "            AND info1.T03_KYOUEKIHI IS null";
        $sql .= "        ) THEN '-'";
        $sql .= "        ELSE TO_CHAR(NVL(info1.T03_KANRIHI, 0) + NVL(info1.T03_KYOUEKIHI, 0)) || '円'";
        $sql .= "        END AS T03_KANRI_KYOEKI_SUM";
        $sql .= "    ,info1.T03_TATEMONO_TYPE";
        $sql .= "    ,info1.T03_SENYUU_MENSEKI";
        $sql .= "    ,info1.T03_MADORI";
        $sql .= "    ,info1.T03_TATEMONO_TYPE";
        $sql .= "    ,info1.T03_KANSEINEN";
        $sql .= "    ,info1.T03_KANSEITSUKI";
        $sql .= "    ,info1.T03_ENSEN_CD01";
        $sql .= "    ,info1.T03_ENSEN_NAME";
        $sql .= "    ,info1.T03_EKI_NAME";
        $sql .= "    ,info1.T03_BUS_JYOUSHA";
        $sql .= "    ,info1.T03_BUS_STOP";
        $sql .= "    ,info1.T03_TOHO01";

        return $sql;
    }

    public function convertShinchakuBlock($result)
    {
        $configObject = Zend_Registry::get('config');
        $config = $configObject->apm->toArray();

        $convertData = array();
        foreach ($result as $buken)
        {

            $link = array();
            $link['ID'] = $buken['T03_HEYA_CD'];
            $link['JYUUSHO'] = $buken['T03_JYUUSHO_TEXT'];
            $link['HEYA_CD'] = $buken['T03_HEYA_CD'];
            $link['GAIKAN_GAZOU01'] = $config['apl']['imagesvurl'] . "fs/Original/img/images/" . $buken['T03_TENPO_CD'] . "/" . $buken['T03_GAIKAN_GAZOU01'];
            $link['YACHIN'] = $buken['T03_YACHIN'];
            $link['TATEMONO_TYPE'] = $buken['T03_TATEMONO_TYPE'];
            $link['KANRI_KYOEKI_SUM'] = $buken['T03_KANRI_KYOEKI_SUM'];
            $link['SENYUU_MENSEKI'] = $buken['T03_SENYUU_MENSEKI'];
            $link['MADORI'] = $buken['T03_MADORI'];
            $link['KANSEI_NENGETU'] = $buken['T03_KANSEINEN'] . "/" . $buken['T03_KANSEITSUKI'];
            $link['JYUUSHO_ORG'] = $buken['T03_JYUUSHO_TEXT'];
            $link['ACCESS_01'] = Apm_Bukkenitems::GetTrafficStr($buken);
            $link['TENPO_NAME'] = $buken['T03_TENPO_NAME'];

            $link['URL'] = Apm_Bukkenurl::generateUrlByParametersDetail($link);
            $link['SHOP_URL'] = Apm_Bukkenurl::generateUrlByParametersShopDetail($buken);

            if (strlen($link['JYUUSHO']) > 9)
            {
                $link['JYUUSHO'] = mb_substr($link['JYUUSHO'], 0, 9) . '..';
            }

            $convertData[] = $link;
        }

        return $convertData;
    }

    public function getShinchakuBlockData($cnt, $area_code)
    {
        if (!empty($cnt))
        {
            $db_params = array();
            $db_params[":recordCnt"] = $cnt;
            $sql = "select T03_TATEMONO_CD,T03_JYUUSHO_TEXT,T03_GAIKAN_GAZOU01,T03_TENPO_CD, T03_TODOUFUKEN_CD, T03_SHIKUCHOUSON_CD, T03_CHOUMEI_CD " . " from (select info2.T03_DAIHYO_TATEMONO_CD AS T03_TATEMONO_CD,info2.T03_JYUUSHO_TEXT,info2.T03_GAIKAN_GAZOU01,info2.T03_TENPO_CD" . " ,info2.T03_TODOUFUKEN_CD, info2.T03_SHIKUCHOUSON_CD, info2.T03_CHOUMEI_CD" . " from t03shinchaku_bukken_todoufuken sample(1)" . " info1 inner join t03web_heya info2 on (info1.t03_heya_cd=info2.t03_heya_cd) ";

            if (isset($area_code))
            {
                $sql .= " WHERE info1.T03_TODOUFUKEN_CD = '" . $area_code . "'";
            }

            $sql .= ")";

            $sql .= " ORDER BY DBMS_RANDOM.RANDOM";
            $sql = "SELECT * FROM (" . $sql . ") where rownum<=:recordCnt";

            $rs = Apm_Util::dbGetList($sql, $db_params, $db);

            $data = array();
            $config = Zend_Registry::get('config')->apm->toArray();
            foreach ($rs as $r)
            {
                $d = array();
                $d["JUSHO"] = $r['T03_JYUUSHO_TEXT'];
                $tenpo_cd = Apm_ShopUtil::getOldTenpoCd($r['T03_TENPO_CD']);
                $d["GAZOU"] = $config['apl']['imagesvurl'] . "fs/Original/img/images/" . $tenpo_cd . "/" . $r["T03_GAIKAN_GAZOU01"];

                $prefName = Apm_Config_Search::$TODOUFUKEN_NAME_CODE_LIST[$r['T03_TODOUFUKEN_CD']];
                $d["URL"] = $config['baseurl'] . $prefName . "/" . $r['T03_SHIKUCHOUSON_CD'] . $r['T03_CHOUMEI_CD'] . "/b" . $r['T03_TATEMONO_CD'] . "/";
                $data[] = $d;
            }
            return $data;
        }
        return array();
    }

}
